{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "a1ca0f55-025a-4025-83b1-05a35a312945",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os, time, datetime, ast, json\n",
    "from itertools import product\n",
    "import geopy.distance as geodist\n",
    "\n",
    "import googlemaps\n",
    "\n",
    "# change the working directory to Build\n",
    "os.chdir(\"/Users/xiaosongw/Dropbox/Research/InformedSources/Replication/Build\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1988fa62-b5b9-4c76-994d-9a384b0af5bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "def myf_get_address(x):  \n",
    "    x = json.loads(x)\n",
    "    try:\n",
    "        out_add = x['formatted_address'].upper() \n",
    "    except: out_add = ''\n",
    "    try: \n",
    "        out_lat = round(x['geometry']['location']['lat'], 8)\n",
    "    except: out_lat = np.nan\n",
    "    try: \n",
    "        out_lng = round(x['geometry']['location']['lng'], 8)\n",
    "    except: out_lng = np.nan\n",
    "    try: \n",
    "        out_list = [i for i in x['address_components'] if 'post' in i['types'][0]]\n",
    "        out_post = int(out_list[0]['long_name'])\n",
    "    except: out_post = np.nan\n",
    "    return pd.Series((out_add, (out_lat, out_lng), out_post))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "58323381-f2dd-4a91-ad38-9e25723d817f",
   "metadata": {},
   "source": [
    "# Sample 20150801 to 20170801\n",
    "\n",
    "## obtain gmap addresses and geocoordinates using Google Map API\n",
    "\n",
    "The following code requires Google Map Geocoding API to run. Please follow the documentation at https://developers.google.com/maps/documentation/geocoding "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f3928617-676a-4950-b45c-8821dd1b1013",
   "metadata": {},
   "outputs": [],
   "source": [
    "df0 = pd.read_excel(\"./Input/AvgUlpPricesVicSites.xlsx\", parse_dates=['Date'])\n",
    "df0.columns = df0.columns.str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "17bdfe81-c5cc-4724-a9ea-8dc6a72da26b",
   "metadata": {},
   "outputs": [],
   "source": [
    "indf = df0.groupby(['sitecode', 'brand', 'sitename', 'address1', 'suburb', 'postcode'])['date'].apply(\n",
    "    lambda x: '{:%Y-%m-%d}/{:%Y-%m-%d}'.format(x.min(), x.max())).reset_index()\n",
    "indf['address1'] = indf['address1'] + ', ' + indf['suburb'] + ', VIC ' + indf['postcode'].astype(str)\n",
    "indf['address2'] = indf['sitename']+', '+indf['address1']\n",
    "indf.sort_values('sitecode', ignore_index=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "40f2c60a-d6d5-4647-a2e2-5d5638f0e6cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "l_add1 = indf.address1.unique().tolist()\n",
    "d_gmap1 = {}\n",
    "\n",
    "gmaps = googlemaps.Client(key='Your_API_Key')\n",
    "for i in range(len(l_add1)):\n",
    "    iadd = l_add1[i]\n",
    "    try: \n",
    "        geocode_result = gmaps.geocode(iadd)\n",
    "    except:\n",
    "        geocode_result = []\n",
    "    if i % 200 == 1:\n",
    "        print('address ' + str(i) + ' completed!')\n",
    "    if len(geocode_result)>0:\n",
    "        d_gmap1[iadd] = json.dumps(geocode_result[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "df5073ed-6b4d-4eb9-9891-b63a9c19cfbc",
   "metadata": {},
   "outputs": [],
   "source": [
    "print('completed {}'.format(len(d_gmap1.keys())))\n",
    "indf['gmap1'] = indf['address1'].map(d_gmap1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "360b5182-261b-485e-a7ae-8117c3e4be36",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "l_add2 = indf.address2.unique().tolist()\n",
    "d_gmap2 = {}\n",
    "\n",
    "gmaps = googlemaps.Client(key='Your_API_Key')\n",
    "for i in range(len(l_add2)):\n",
    "    iadd = l_add2[i]\n",
    "    try: \n",
    "        geocode_result = gmaps.geocode(iadd)\n",
    "    except:\n",
    "        geocode_result = []\n",
    "    if i % 200 == 1:\n",
    "        print('address ' + str(i) + ' completed!')\n",
    "    if len(geocode_result)>0:\n",
    "        d_gmap2[iadd] = json.dumps(geocode_result[0])\n",
    "\n",
    "print('completed {}'.format(len(d_gmap2.keys())))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bf24986f-ec5e-459c-ad5d-b4f29c9330c8",
   "metadata": {},
   "outputs": [],
   "source": [
    "indf['gmap2'] = indf['address2'].map(d_gmap2)\n",
    "display(indf[indf['gmap2'].isnull()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "791e795c-edd5-4d68-8ebb-253093c9a20a",
   "metadata": {},
   "outputs": [],
   "source": [
    "indf.sort_values(['sitecode', 'date'], ignore_index=True, inplace=True)\n",
    "indf['bn_period'] = indf['brand']+':'+indf['date']\n",
    "\n",
    "# one sitecode uniquely correspond to address1 and address2\n",
    "outdf = indf[['sitecode', 'sitename', 'address1', 'address2', 'gmap1', 'gmap2']].drop_duplicates().copy()\n",
    "outdf['bn_prd'] = outdf['sitecode'].map(indf.groupby('sitecode')['bn_period'].apply(lambda x: '; '.join(x)).to_dict())\n",
    "print(outdf.shape)\n",
    "display(outdf.head(3))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a33b547b-9e59-449f-8b5a-cd4aa8fff684",
   "metadata": {},
   "source": [
    "## Clean addresses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9d9c8348-03e8-4dbb-b008-041ea0ee7cdc",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_gadd = outdf.copy()\n",
    "print(df_gadd.nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "953bf9b0-a7fc-4d6a-9921-000642e28c07",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_gadd[['gadd1', 'coor1', 'gpost1']] = df_gadd['gmap1'].apply(lambda x: myf_get_address(x))\n",
    "df_gadd[['gadd2', 'coor2', 'gpost2']] = df_gadd['gmap2'].apply(lambda x: myf_get_address(x))\n",
    "df_gadd.drop(['gpost1', 'gpost2'], axis=1, inplace=True)\n",
    "df_gadd['dist'] = df_gadd[['coor1', 'coor2']].apply(lambda x: geodist.distance(x[0], x[1]).m, axis=1)\n",
    "l_match1 = df_gadd.loc[df_gadd['dist']<=50, 'sitecode'].unique().tolist()\n",
    "print('1st round by distance, matched codes: {}'.format(len(l_match1)))\n",
    "\n",
    "d_matches = {}\n",
    "d_matches.update(df_gadd[df_gadd['sitecode'].isin(l_match1)].set_index('sitecode')['gmap2'].to_dict())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a6304e74-e404-4966-a1a4-d798c4ca4ef8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_gadd.loc[df_gadd['dist']>50, \n",
    "            ['sitecode', 'sitename', 'address1', 'bn_prd', 'gadd1', 'gadd2', 'coor1', 'coor2', 'dist']].sort_values(\n",
    "    ['address1', 'dist'], ignore_index=True).to_excel('./Temp/zmanual_is_vic_gmap_clean.xlsx', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "71b2a504-4348-4000-b220-dd9417b79d34",
   "metadata": {},
   "outputs": [],
   "source": [
    "# update addresses based on manual check \n",
    "d_matches.update({61303427:json.dumps({'address_components': [{'long_name': '397',\n",
    "    'short_name': '397',\n",
    "    'types': ['street_number']},\n",
    "   {'long_name': 'Princes Highway',\n",
    "    'short_name': 'Princes Hwy',\n",
    "    'types': ['route']},\n",
    "   {'long_name': 'Officer',\n",
    "    'short_name': 'Officer',\n",
    "    'types': ['locality', 'political']},\n",
    "   {'long_name': 'Cardinia Shire',\n",
    "    'short_name': 'Cardinia',\n",
    "    'types': ['administrative_area_level_2', 'political']},\n",
    "   {'long_name': 'Victoria',\n",
    "    'short_name': 'VIC',\n",
    "    'types': ['administrative_area_level_1', 'political']},\n",
    "   {'long_name': 'Australia',\n",
    "    'short_name': 'AU',\n",
    "    'types': ['country', 'political']},\n",
    "   {'long_name': '3809', 'short_name': '3809', 'types': ['postal_code']}],\n",
    "  'formatted_address': '397 Princes Hwy, Officer VIC 3809, Australia',\n",
    "  'geometry': {'location': {'lat': -38.0592157, 'lng': 145.4065302},\n",
    "   'location_type': 'ROOFTOP',\n",
    "   'viewport': {'northeast': {'lat': -38.0578667197085,\n",
    "     'lng': 145.4078791802915},\n",
    "    'southwest': {'lat': -38.0605646802915, 'lng': 145.4051812197085}}},\n",
    "  'place_id': 'ChIJIQlKPAEc1moRhFb_qa9nJ-E',\n",
    "  'types': ['atm',\n",
    "   'bakery',\n",
    "   'convenience_store',\n",
    "   'establishment',\n",
    "   'finance',\n",
    "   'food',\n",
    "   'gas_station',\n",
    "   'point_of_interest',\n",
    "   'restaurant',\n",
    "   'store']}),\n",
    " 61303439:json.dumps({'address_components': [{'long_name': '496',\n",
    "    'short_name': '496',\n",
    "    'types': ['street_number']},\n",
    "   {'long_name': 'Princes Highway',\n",
    "    'short_name': 'Princes Hwy',\n",
    "    'types': ['route']},\n",
    "   {'long_name': 'Officer',\n",
    "    'short_name': 'Officer',\n",
    "    'types': ['locality', 'political']},\n",
    "   {'long_name': 'Cardinia Shire',\n",
    "    'short_name': 'Cardinia',\n",
    "    'types': ['administrative_area_level_2', 'political']},\n",
    "   {'long_name': 'Victoria',\n",
    "    'short_name': 'VIC',\n",
    "    'types': ['administrative_area_level_1', 'political']},\n",
    "   {'long_name': 'Australia',\n",
    "    'short_name': 'AU',\n",
    "    'types': ['country', 'political']},\n",
    "   {'long_name': '3809', 'short_name': '3809', 'types': ['postal_code']}],\n",
    "  'formatted_address': '496 Princes Hwy, Officer VIC 3809, Australia',\n",
    "  'geometry': {'location': {'lat': -38.0621061, 'lng': 145.4167842},\n",
    "   'location_type': 'ROOFTOP',\n",
    "   'viewport': {'northeast': {'lat': -38.06075711970851,\n",
    "     'lng': 145.4181331802915},\n",
    "    'southwest': {'lat': -38.0634550802915, 'lng': 145.4154352197085}}},\n",
    "  'place_id': 'ChIJ53K5nhsc1moR8RBooIY_pB0',\n",
    "  'types': ['atm',\n",
    "   'bakery',\n",
    "   'convenience_store',\n",
    "   'establishment',\n",
    "   'finance',\n",
    "   'food',\n",
    "   'gas_station',\n",
    "   'point_of_interest',\n",
    "   'restaurant',\n",
    "   'store']}),\n",
    " 61302758:json.dumps({'address_components': [{'long_name': '14',\n",
    "    'short_name': '14',\n",
    "    'types': ['subpremise']},\n",
    "   {'long_name': '16', 'short_name': '16', 'types': ['street_number']},\n",
    "   {'long_name': 'High Street', 'short_name': 'High St', 'types': ['route']},\n",
    "   {'long_name': 'Lismore',\n",
    "    'short_name': 'Lismore',\n",
    "    'types': ['locality', 'political']},\n",
    "   {'long_name': 'Corangamite Shire',\n",
    "    'short_name': 'Corangamite',\n",
    "    'types': ['administrative_area_level_2', 'political']},\n",
    "   {'long_name': 'Victoria',\n",
    "    'short_name': 'VIC',\n",
    "    'types': ['administrative_area_level_1', 'political']},\n",
    "   {'long_name': 'Australia',\n",
    "    'short_name': 'AU',\n",
    "    'types': ['country', 'political']},\n",
    "   {'long_name': '3324', 'short_name': '3324', 'types': ['postal_code']}],\n",
    "  'formatted_address': '14/16 High St, Lismore VIC 3324, Australia',\n",
    "  'geometry': {'location': {'lat': -37.9536826, 'lng': 143.3452079},\n",
    "   'location_type': 'ROOFTOP',\n",
    "   'viewport': {'northeast': {'lat': -37.9523336197085,\n",
    "     'lng': 143.3465568802915},\n",
    "    'southwest': {'lat': -37.9550315802915, 'lng': 143.3438589197085}}},\n",
    "  'place_id': 'ChIJIfikH19M0moRTdGPMSgnG2o',\n",
    "  'plus_code': {'compound_code': '28WW+G3 Lismore VIC, Australia',\n",
    "   'global_code': '4RJ528WW+G3'},\n",
    "  'types': ['car_repair',\n",
    "   'establishment',\n",
    "   'gas_station',\n",
    "   'point_of_interest']}),\n",
    " 61377146:json.dumps({'address_components': [{'long_name': 'Bannockburn',\n",
    "    'short_name': 'Bannockburn',\n",
    "    'types': ['locality', 'political']},\n",
    "   {'long_name': 'Golden Plains Shire',\n",
    "    'short_name': 'Golden Plains',\n",
    "    'types': ['administrative_area_level_2', 'political']},\n",
    "   {'long_name': 'Victoria',\n",
    "    'short_name': 'VIC',\n",
    "    'types': ['administrative_area_level_1', 'political']},\n",
    "   {'long_name': 'Australia',\n",
    "    'short_name': 'AU',\n",
    "    'types': ['country', 'political']},\n",
    "   {'long_name': '3331', 'short_name': '3331', 'types': ['postal_code']}],\n",
    "  'formatted_address': 'Cnr Midland Hwy & Shelford-Bannockbur, Bannockburn VIC 3331, Australia',\n",
    "  'geometry': {'location': {'lat': -38.0481826, 'lng': 144.1981205},\n",
    "   'location_type': 'GEOMETRIC_CENTER',\n",
    "   'viewport': {'northeast': {'lat': -38.0468336197085,\n",
    "     'lng': 144.1994694802915},\n",
    "    'southwest': {'lat': -38.0495315802915, 'lng': 144.1967715197085}}},\n",
    "  'place_id': 'ChIJDSGIjx4P1GoRHzlG2HEySBc',\n",
    "  'plus_code': {'compound_code': 'X52X+P6 Bannockburn VIC, Australia',\n",
    "   'global_code': '4RH6X52X+P6'},\n",
    "  'types': ['establishment', 'gas_station', 'point_of_interest']}),\n",
    " 61377075:np.nan,\n",
    " 61302703:json.dumps({'address_components': [{'long_name': '10',\n",
    "    'short_name': '10',\n",
    "    'types': ['street_number']},\n",
    "   {'long_name': 'Heaths Court',\n",
    "    'short_name': 'Heaths Ct',\n",
    "    'types': ['route']},\n",
    "   {'long_name': 'South Morang',\n",
    "    'short_name': 'South Morang',\n",
    "    'types': ['locality', 'political']},\n",
    "   {'long_name': 'City of Whittlesea',\n",
    "    'short_name': 'City of Whittlesea',\n",
    "    'types': ['administrative_area_level_2', 'political']},\n",
    "   {'long_name': 'Victoria',\n",
    "    'short_name': 'VIC',\n",
    "    'types': ['administrative_area_level_1', 'political']},\n",
    "   {'long_name': 'Australia',\n",
    "    'short_name': 'AU',\n",
    "    'types': ['country', 'political']},\n",
    "   {'long_name': '3752', 'short_name': '3752', 'types': ['postal_code']}],\n",
    "  'formatted_address': '10 Heaths Ct, South Morang VIC 3752, Australia',\n",
    "  'geometry': {'location': {'lat': -37.6558509, 'lng': 145.0815933},\n",
    "   'location_type': 'ROOFTOP',\n",
    "   'viewport': {'northeast': {'lat': -37.6545019197085,\n",
    "     'lng': 145.0829422802915},\n",
    "    'southwest': {'lat': -37.6571998802915, 'lng': 145.0802443197085}}},\n",
    "  'place_id': 'ChIJi_y-5G9J1moRYfgEVRXMqdI',\n",
    "  'plus_code': {'compound_code': '83VJ+MJ South Morang VIC, Australia',\n",
    "   'global_code': '4RJ783VJ+MJ'},\n",
    "  'types': ['establishment', 'gas_station', 'point_of_interest']}),\n",
    " 61302032:json.dumps({'address_components': [{'long_name': 'Dorset Road',\n",
    "    'short_name': 'Dorset Rd',\n",
    "    'types': ['route']},\n",
    "   {'long_name': 'Bayswater North',\n",
    "    'short_name': 'Bayswater North',\n",
    "    'types': ['locality', 'political']},\n",
    "   {'long_name': 'Maroondah City',\n",
    "    'short_name': 'Maroondah',\n",
    "    'types': ['administrative_area_level_2', 'political']},\n",
    "   {'long_name': 'Victoria',\n",
    "    'short_name': 'VIC',\n",
    "    'types': ['administrative_area_level_1', 'political']},\n",
    "   {'long_name': 'Australia',\n",
    "    'short_name': 'AU',\n",
    "    'types': ['country', 'political']},\n",
    "   {'long_name': '3153', 'short_name': '3153', 'types': ['postal_code']}],\n",
    "  'formatted_address': '148-150 Canterbury Rd (Corner, Dorset Rd, Bayswater North VIC 3153, Australia',\n",
    "  'geometry': {'location': {'lat': -37.8252567, 'lng': 145.2883456},\n",
    "   'location_type': 'GEOMETRIC_CENTER',\n",
    "   'viewport': {'northeast': {'lat': -37.8239077197085,\n",
    "     'lng': 145.2896945802915},\n",
    "    'southwest': {'lat': -37.8266056802915, 'lng': 145.2869966197085}}},\n",
    "  'place_id': 'ChIJtdZFceE61moRMElAn9hohkY',\n",
    "  'plus_code': {'compound_code': '57FQ+V8 Bayswater North VIC, Australia',\n",
    "   'global_code': '4RJ757FQ+V8'},\n",
    "  'types': ['convenience_store',\n",
    "   'establishment',\n",
    "   'food',\n",
    "   'gas_station',\n",
    "   'point_of_interest',\n",
    "   'store']}),\n",
    " 61370424:json.dumps({'address_components': [{'long_name': 'Hume Freeway',\n",
    "    'short_name': 'Hume Fwy',\n",
    "    'types': ['route']},\n",
    "   {'long_name': 'Glenrowan',\n",
    "    'short_name': 'Glenrowan',\n",
    "    'types': ['locality', 'political']},\n",
    "   {'long_name': 'Wangaratta Rural City',\n",
    "    'short_name': 'Wangaratta',\n",
    "    'types': ['administrative_area_level_2', 'political']},\n",
    "   {'long_name': 'Victoria',\n",
    "    'short_name': 'VIC',\n",
    "    'types': ['administrative_area_level_1', 'political']},\n",
    "   {'long_name': 'Australia',\n",
    "    'short_name': 'AU',\n",
    "    'types': ['country', 'political']},\n",
    "   {'long_name': '3675', 'short_name': '3675', 'types': ['postal_code']}],\n",
    "  'formatted_address': 'Hume Fwy, Glenrowan VIC 3675, Australia',\n",
    "  'geometry': {'location': {'lat': -36.4391262, 'lng': 146.2481194},\n",
    "   'location_type': 'GEOMETRIC_CENTER',\n",
    "   'viewport': {'northeast': {'lat': -36.4377772197085,\n",
    "     'lng': 146.2494683802915},\n",
    "    'southwest': {'lat': -36.4404751802915, 'lng': 146.2467704197085}}},\n",
    "  'place_id': 'ChIJI30dxUXgJmsRQbj-cLCl3NE',\n",
    "  'plus_code': {'compound_code': 'H66X+86 Glenrowan VIC, Australia',\n",
    "   'global_code': '4RM8H66X+86'},\n",
    "  'types': ['atm',\n",
    "   'cafe',\n",
    "   'establishment',\n",
    "   'finance',\n",
    "   'food',\n",
    "   'gas_station',\n",
    "   'point_of_interest',\n",
    "   'restaurant']}),\n",
    " 61370485:json.dumps({'address_components': [{'long_name': 'Wangaratta South',\n",
    "    'short_name': 'Wangaratta South',\n",
    "    'types': ['locality', 'political']},\n",
    "   {'long_name': 'Wangaratta Rural City',\n",
    "    'short_name': 'Wangaratta',\n",
    "    'types': ['administrative_area_level_2', 'political']},\n",
    "   {'long_name': 'Victoria',\n",
    "    'short_name': 'VIC',\n",
    "    'types': ['administrative_area_level_1', 'political']},\n",
    "   {'long_name': 'Australia',\n",
    "    'short_name': 'AU',\n",
    "    'types': ['country', 'political']},\n",
    "   {'long_name': '3678', 'short_name': '3678', 'types': ['postal_code']}],\n",
    "  'formatted_address': 'Wangaratta South VIC 3678, Australia',\n",
    "  'geometry': {'location': {'lat': -36.4394913, 'lng': 146.2499145},\n",
    "   'location_type': 'GEOMETRIC_CENTER',\n",
    "   'viewport': {'northeast': {'lat': -36.4381423197085,\n",
    "     'lng': 146.2512634802915},\n",
    "    'southwest': {'lat': -36.4408402802915, 'lng': 146.2485655197085}}},\n",
    "  'place_id': 'ChIJB9d9vIDgJmsRjWEReUoAszk',\n",
    "  'types': ['establishment', 'gas_station', 'point_of_interest']}),\n",
    " 61301983:json.dumps({'address_components': [{'long_name': '89',\n",
    "    'short_name': '89',\n",
    "    'types': ['street_number']},\n",
    "   {'long_name': 'Thomas Mitchell Drive',\n",
    "    'short_name': 'Thomas Mitchell Dr',\n",
    "    'types': ['route']},\n",
    "   {'long_name': 'Wodonga',\n",
    "    'short_name': 'Wodonga',\n",
    "    'types': ['locality', 'political']},\n",
    "   {'long_name': 'City of Wodonga',\n",
    "    'short_name': 'City of Wodonga',\n",
    "    'types': ['administrative_area_level_2', 'political']},\n",
    "   {'long_name': 'Victoria',\n",
    "    'short_name': 'VIC',\n",
    "    'types': ['administrative_area_level_1', 'political']},\n",
    "   {'long_name': 'Australia',\n",
    "    'short_name': 'AU',\n",
    "    'types': ['country', 'political']},\n",
    "   {'long_name': '3690', 'short_name': '3690', 'types': ['postal_code']}],\n",
    "  'formatted_address': '89 Thomas Mitchell Dr, Wodonga VIC 3690, Australia',\n",
    "  'geometry': {'location': {'lat': -36.134846, 'lng': 146.9044259},\n",
    "   'location_type': 'ROOFTOP',\n",
    "   'viewport': {'northeast': {'lat': -36.1334970197085,\n",
    "     'lng': 146.9057748802915},\n",
    "    'southwest': {'lat': -36.1361949802915, 'lng': 146.9030769197085}}},\n",
    "  'place_id': 'ChIJK3UsO4xYIWsR-ZKuwzxui-4',\n",
    "  'plus_code': {'compound_code': 'VW83+3Q Wodonga VIC, Australia',\n",
    "   'global_code': '4RM8VW83+3Q'},\n",
    "  'types': ['establishment', 'gas_station', 'point_of_interest']})})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "19eb50d8-cd11-49fc-bb33-4d849c82841b",
   "metadata": {},
   "outputs": [],
   "source": [
    "d_manu = {61301752: 2,\n",
    " 61301174: 2,\n",
    " 61303427: 0,\n",
    " 61303705: 2,\n",
    " 61303439: 0,\n",
    " 61377039: 2,\n",
    " 61302587: 2,\n",
    " 61377121: 2,\n",
    " 61303651: 2,\n",
    " 61301102: 1,\n",
    " 61303542: 1,\n",
    " 61301834: 1,\n",
    " 61302812: 2,\n",
    " 61302047: 2,\n",
    " 61303248: 2,\n",
    " 61302561: 1,\n",
    " 61377147: 1,\n",
    " 61351567: 2,\n",
    " 61377102: 2,\n",
    " 61302758: 0,\n",
    " 61377149: 2,\n",
    " 61302616: 2,\n",
    " 61301536: 2,\n",
    " 61302516: 2,\n",
    " 61303689: 1,\n",
    " 61303050: 2,\n",
    " 61302667: 2,\n",
    " 61377090: 1,\n",
    " 61303669: 2,\n",
    " 61302267: 1,\n",
    " 61302517: 2,\n",
    " 61302718: 2,\n",
    " 61302260: 2,\n",
    " 61377146: 0,\n",
    " 61303093: 1,\n",
    " 61301975: 1,\n",
    " 61301930: 2,\n",
    " 61303697: 2,\n",
    " 61301431: 2,\n",
    " 61370181: 2,\n",
    " 61351472: 2,\n",
    " 61302474: 2,\n",
    " 61301439: 2,\n",
    " 61302559: 2,\n",
    " 61377104: 2,\n",
    " 61302730: 2,\n",
    " 61303433: 2,\n",
    " 61377071: 2,\n",
    " 61377075: 0,\n",
    " 61302711: 2,\n",
    " 61302387: 2,\n",
    " 61302042: 2,\n",
    " 61301692: 2,\n",
    " 61370399: 2,\n",
    " 61303022: 2,\n",
    " 61302437: 1,\n",
    " 61377805: 2,\n",
    " 61302171: 2,\n",
    " 61303059: 2,\n",
    " 61301904: 2,\n",
    " 61302700: 2,\n",
    " 61302746: 2,\n",
    " 61302208: 2,\n",
    " 61301035: 2,\n",
    " 61301754: 1,\n",
    " 61377144: 2,\n",
    " 61302419: 1,\n",
    " 61302689: 2,\n",
    " 61301931: 2,\n",
    " 61301735: 2,\n",
    " 61302117: 1,\n",
    " 61303714: 2,\n",
    " 61302253: 1,\n",
    " 61303442: 2,\n",
    " 61302715: 2,\n",
    " 61301383: 2,\n",
    " 61302176: 2,\n",
    " 61302169: 2,\n",
    " 61302807: 2,\n",
    " 61301681: 2,\n",
    " 61301744: 2,\n",
    " 61301360: 2,\n",
    " 61303649: 2,\n",
    " 61302556: 2,\n",
    " 61302826: 2,\n",
    " 61302495: 2,\n",
    " 61301292: 2,\n",
    " 61377101: 2,\n",
    " 61300009: 2,\n",
    " 61377796: 2,\n",
    " 61370096: 1,\n",
    " 61301467: 2,\n",
    " 61303728: 2,\n",
    " 61301900: 2,\n",
    " 61351431: 2,\n",
    " 61301940: 2,\n",
    " 61301450: 2,\n",
    " 61377717: 2,\n",
    " 61301822: 2,\n",
    " 61351385: 2,\n",
    " 61301710: 2,\n",
    " 61303100: 2,\n",
    " 61303284: 2,\n",
    " 61303661: 2,\n",
    " 61301367: 2,\n",
    " 61351529: 2,\n",
    " 61301626: 2,\n",
    " 61302703: 0,\n",
    " 61303616: 2,\n",
    " 61301231: 1,\n",
    " 61351413: 2,\n",
    " 61377804: 2,\n",
    " 61302296: 2,\n",
    " 61302583: 2,\n",
    " 61301708: 2,\n",
    " 61303696: 2,\n",
    " 61302319: 2,\n",
    " 61303675: 2,\n",
    " 61301462: 2,\n",
    " 61301070: 2,\n",
    " 61302276: 2,\n",
    " 61302263: 2,\n",
    " 61303702: 2,\n",
    " 61301179: 2,\n",
    " 61301500: 1,\n",
    " 61302271: 2,\n",
    " 61351358: 2,\n",
    " 61303239: 1,\n",
    " 61303150: 2,\n",
    " 61301630: 2,\n",
    " 61302557: 2,\n",
    " 61301551: 1,\n",
    " 61303686: 2,\n",
    " 61303368: 2,\n",
    " 61302787: 2,\n",
    " 61301504: 2,\n",
    " 61301532: 2,\n",
    " 61303585: 2,\n",
    " 61302568: 2,\n",
    " 61377056: 2,\n",
    " 61301951: 2,\n",
    " 61301317: 2,\n",
    " 61302241: 2,\n",
    " 61303000: 2,\n",
    " 61303195: 2,\n",
    " 61301688: 2,\n",
    " 61301440: 2,\n",
    " 61300002: 2,\n",
    " 61303547: 2,\n",
    " 61303190: 2,\n",
    " 61303228: 2,\n",
    " 61303684: 2,\n",
    " 61303254: 2,\n",
    " 61301121: 2,\n",
    " 61302712: 2,\n",
    " 61301481: 2,\n",
    " 61303615: 2,\n",
    " 61370012: 2,\n",
    " 61302032: 0,\n",
    " 61302293: 2,\n",
    " 61302578: 2,\n",
    " 61301180: 2,\n",
    " 61303140: 2,\n",
    " 61301442: 2,\n",
    " 61303123: 2,\n",
    " 61303650: 2,\n",
    " 61377814: 2,\n",
    " 61301188: 2,\n",
    " 61370279: 2,\n",
    " 61300005: 2,\n",
    " 61301232: 2,\n",
    " 61303643: 2,\n",
    " 61303306: 2,\n",
    " 61303127: 2,\n",
    " 61302454: 2,\n",
    " 61302563: 2,\n",
    " 61301347: 2,\n",
    " 61303570: 2,\n",
    " 61351393: 2,\n",
    " 61301451: 2,\n",
    " 61377052: 2,\n",
    " 61301220: 2,\n",
    " 61302283: 2,\n",
    " 61303023: 2,\n",
    " 61302811: 2,\n",
    " 61301642: 2,\n",
    " 61370639: 2,\n",
    " 61303149: 2,\n",
    " 61303132: 2,\n",
    " 61303387: 2,\n",
    " 61303009: 2,\n",
    " 61302205: 2,\n",
    " 61301409: 2,\n",
    " 61301711: 2,\n",
    " 61302762: 2,\n",
    " 61301271: 2,\n",
    " 61301765: 2,\n",
    " 61303414: 2,\n",
    " 61302328: 2,\n",
    " 61303259: 2,\n",
    " 61302311: 2,\n",
    " 61303277: 2,\n",
    " 61303272: 2,\n",
    " 61302819: 2,\n",
    " 61303126: 2,\n",
    " 61301233: 2,\n",
    " 61301186: 2,\n",
    " 61302180: 2,\n",
    " 61302018: 2,\n",
    " 61303656: 2,\n",
    " 61301253: 2,\n",
    " 61301250: 2,\n",
    " 61301391: 2,\n",
    " 61370424: 0,\n",
    " 61370485: 0,\n",
    " 61302413: 2,\n",
    " 61302318: 2,\n",
    " 61303724: 2,\n",
    " 61302665: 2,\n",
    " 61302714: 2,\n",
    " 61301104: 2,\n",
    " 61301106: 2,\n",
    " 61301364: 2,\n",
    " 61301436: 2,\n",
    " 61301072: 2,\n",
    " 61402774: 2,\n",
    " 61370597: 2,\n",
    " 61302522: 2,\n",
    " 61370591: 2,\n",
    " 61351536: 2,\n",
    " 61303671: 2,\n",
    " 61301763: 2,\n",
    " 61377152: 2,\n",
    " 61301749: 2,\n",
    " 61377051: 2,\n",
    " 61301290: 2,\n",
    " 61302502: 2,\n",
    " 61302639: 2,\n",
    " 61301055: 2,\n",
    " 61302755: 2,\n",
    " 61303145: 2,\n",
    " 61302168: 2,\n",
    " 61302301: 2,\n",
    " 61303700: 2,\n",
    " 61301499: 2,\n",
    " 61351407: 2,\n",
    " 61301758: 2,\n",
    " 61301189: 2,\n",
    " 61301251: 2,\n",
    " 61301301: 2,\n",
    " 61301057: 2,\n",
    " 61301299: 2,\n",
    " 61301313: 2,\n",
    " 61301016: 2,\n",
    " 61303250: 2,\n",
    " 61303644: 2,\n",
    " 61301099: 2,\n",
    " 61301034: 2,\n",
    " 61302289: 2,\n",
    " 61301753: 2,\n",
    " 61301743: 2,\n",
    " 61301633: 2,\n",
    " 61370089: 2,\n",
    " 61370644: 2,\n",
    " 61301713: 2,\n",
    " 61302786: 2,\n",
    " 61301132: 2,\n",
    " 61302708: 2,\n",
    " 61303162: 2,\n",
    " 61303161: 2,\n",
    " 61303217: 2,\n",
    " 61303222: 2,\n",
    " 61303332: 2,\n",
    " 61302036: 2,\n",
    " 61351322: 2,\n",
    " 61302181: 2,\n",
    " 61302507: 2,\n",
    " 61303295: 2,\n",
    " 61301983: 0,\n",
    " 61300003: 2,\n",
    " 61302627: 2,\n",
    " 61303322: 2,\n",
    " 61302664: 2,\n",
    " 61302175: 2,\n",
    " 61303659: 2,\n",
    " 61303393: 2,\n",
    " 61377030: 2,\n",
    " 61303386: 2,\n",
    " 61303394: 2,\n",
    " 61303285: 2,\n",
    " 61303270: 2,\n",
    " 61351435: 2,\n",
    " 61370494: 2,\n",
    " 61377091: 2,\n",
    " 61301069: 2,\n",
    " 61301120: 2,\n",
    " 61303662: 2,\n",
    " 61303712: 2,\n",
    " 61302719: 2,\n",
    " 61303555: 2,\n",
    " 61302593: 2,\n",
    " 61303158: 2,\n",
    " 61303727: 2,\n",
    " 61303178: 2,\n",
    " 61302736: 2,\n",
    " 61351505: 2,\n",
    " 61303232: 2,\n",
    " 61303235: 2,\n",
    " 61302382: 2,\n",
    " 61302219: 2,\n",
    " 61302393: 2,\n",
    " 61301528: 2,\n",
    " 61301771: 2,\n",
    " 61301144: 2,\n",
    " 61301256: 2,\n",
    " 61303138: 2,\n",
    " 61302361: 2,\n",
    " 61302512: 2,\n",
    " 61302605: 2,\n",
    " 61302687: 2,\n",
    " 61301373: 2,\n",
    " 61301823: 2,\n",
    " 61301319: 2,\n",
    " 61301958: 2,\n",
    " 61301085: 2,\n",
    " 61302182: 2,\n",
    " 61301495: 2,\n",
    " 61303244: 2,\n",
    " 61302120: 2,\n",
    " 61302363: 2,\n",
    " 61301448: 2,\n",
    " 61301505: 2,\n",
    " 61370631: 2,\n",
    " 61302710: 2,\n",
    " 61303726: 2,\n",
    " 61302725: 2,\n",
    " 61301350: 2,\n",
    " 61351537: 2}\n",
    "l_manu_gmap1 = [i for i in d_manu.keys() if d_manu[i]==1]\n",
    "l_manu_gmap2 = [i for i in d_manu.keys() if d_manu[i]==2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "188109d2-8969-4356-adb7-4c1cafd898ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "d_matches.update(df_gadd[df_gadd['sitecode'].isin(l_manu_gmap1)].set_index('sitecode')['gmap1'].to_dict())\n",
    "d_matches.update(df_gadd[df_gadd['sitecode'].isin(l_manu_gmap2)].set_index('sitecode')['gmap2'].to_dict())\n",
    "display([i for i in df_gadd['sitecode'].unique().tolist() if i not in d_matches.keys()])\n",
    "# all sitecodes have been cleaned "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ab6d80b4-669b-4bfe-9236-12f553339033",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_out = df_gadd[['sitecode', 'sitename', 'address1', 'bn_prd']].copy()\n",
    "df_out['gmap'] = df_out['sitecode'].map(d_matches)\n",
    "display(df_out[df_out['gmap'].isnull()])\n",
    "df_out = df_out[df_out['gmap'].notnull()].copy()\n",
    "df_out[['gadd', 'coor', 'gpost']] = df_out['gmap'].apply(lambda x: myf_get_address(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d4490255-4238-4d48-93e0-d1254fe15c0f",
   "metadata": {},
   "outputs": [],
   "source": [
    "display(df_out[df_out.groupby('gadd')['sitecode'].transform('count')>1].sort_values('gadd'))\n",
    "l_check = df_out.loc[df_out.groupby('gadd')['sitecode'].transform('count')>1, 'gadd'].unique().tolist()\n",
    "l_check.sort()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "caf064bf-35a7-4b54-9a06-cfe97c24294b",
   "metadata": {},
   "outputs": [],
   "source": [
    "l_check_sitecode1 = [61302078, 61302593]\n",
    "d_matches.update(df_gadd.loc[df_gadd['sitecode'].isin(l_check_sitecode1)].set_index('sitecode')['gmap1'].to_dict())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ec0fcc87-11d1-48f4-9eea-2fc50acc2d23",
   "metadata": {},
   "outputs": [],
   "source": [
    "d_matches.update({\n",
    "    61301070:np.nan,\n",
    "    61303726:np.nan,\n",
    "    61302736:json.dumps({'address_components': [{'long_name': 'Shop 7A',\n",
    "    'short_name': 'Shop 7A',\n",
    "    'types': ['subpremise']},\n",
    "   {'long_name': '2', 'short_name': '2', 'types': ['street_number']},\n",
    "   {'long_name': 'King Street', 'short_name': 'King St', 'types': ['route']},\n",
    "   {'long_name': 'Korumburra',\n",
    "    'short_name': 'Korumburra',\n",
    "    'types': ['locality', 'political']},\n",
    "   {'long_name': 'South Gippsland Shire',\n",
    "    'short_name': 'South Gippsland',\n",
    "    'types': ['administrative_area_level_2', 'political']},\n",
    "   {'long_name': 'Victoria',\n",
    "    'short_name': 'VIC',\n",
    "    'types': ['administrative_area_level_1', 'political']},\n",
    "   {'long_name': 'Australia',\n",
    "    'short_name': 'AU',\n",
    "    'types': ['country', 'political']},\n",
    "   {'long_name': '3950', 'short_name': '3950', 'types': ['postal_code']}],\n",
    "  'formatted_address': 'Shop 7A/2 King St, Korumburra VIC 3950, Australia',\n",
    "  'geometry': {'location': {'lat': -38.42959, 'lng': 145.82116},\n",
    "   'location_type': 'ROOFTOP',\n",
    "   'viewport': {'northeast': {'lat': -38.42824101970849,\n",
    "     'lng': 145.8225089802915},\n",
    "    'southwest': {'lat': -38.43093898029149, 'lng': 145.8198110197085}}},\n",
    "  'place_id': 'ChIJTbwUA-ofKmsR4sF6oRz88co',\n",
    "  'plus_code': {'compound_code': 'HRCC+5F Korumburra VIC, Australia',\n",
    "   'global_code': '4RH7HRCC+5F'},\n",
    "  'types': ['atm',\n",
    "   'establishment',\n",
    "   'finance',\n",
    "   'food',\n",
    "   'gas_station',\n",
    "   'point_of_interest',\n",
    "   'restaurant']}), \n",
    " 61301442:json.dumps({'address_components': [{'long_name': 'Buch Avenue',\n",
    "    'short_name': 'Buch Ave',\n",
    "    'types': ['route']},\n",
    "   {'long_name': 'Epping',\n",
    "    'short_name': 'Epping',\n",
    "    'types': ['locality', 'political']},\n",
    "   {'long_name': 'City of Whittlesea',\n",
    "    'short_name': 'City of Whittlesea',\n",
    "    'types': ['administrative_area_level_2', 'political']},\n",
    "   {'long_name': 'Victoria',\n",
    "    'short_name': 'VIC',\n",
    "    'types': ['administrative_area_level_1', 'political']},\n",
    "   {'long_name': 'Australia',\n",
    "    'short_name': 'AU',\n",
    "    'types': ['country', 'political']},\n",
    "   {'long_name': '3076', 'short_name': '3076', 'types': ['postal_code']}],\n",
    "  'formatted_address': 'cnr Childs Rd &, Buch Ave, Epping VIC 3076, Australia',\n",
    "  'geometry': {'location': {'lat': -37.660016, 'lng': 145.0243042},\n",
    "   'location_type': 'GEOMETRIC_CENTER',\n",
    "   'viewport': {'northeast': {'lat': -37.6586670197085,\n",
    "     'lng': 145.0256531802915},\n",
    "    'southwest': {'lat': -37.66136498029149, 'lng': 145.0229552197085}}},\n",
    "  'place_id': 'ChIJM02n1uZO1moRt7NRTkpBK4Y',\n",
    "  'plus_code': {'compound_code': '82QF+XP Epping VIC, Australia',\n",
    "   'global_code': '4RJ782QF+XP'},\n",
    "  'types': ['atm',\n",
    "   'bakery',\n",
    "   'convenience_store',\n",
    "   'establishment',\n",
    "   'finance',\n",
    "   'food',\n",
    "   'gas_station',\n",
    "   'point_of_interest',\n",
    "   'restaurant',\n",
    "   'store']}), \n",
    " 61302219:json.dumps({'address_components': [{'long_name': 'Springvale Road',\n",
    "    'short_name': 'Springvale Rd',\n",
    "    'types': ['route']},\n",
    "   {'long_name': 'Glen Waverley',\n",
    "    'short_name': 'Glen Waverley',\n",
    "    'types': ['locality', 'political']},\n",
    "   {'long_name': 'City of Monash',\n",
    "    'short_name': 'City of Monash',\n",
    "    'types': ['administrative_area_level_2', 'political']},\n",
    "   {'long_name': 'Victoria',\n",
    "    'short_name': 'VIC',\n",
    "    'types': ['administrative_area_level_1', 'political']},\n",
    "   {'long_name': 'Australia',\n",
    "    'short_name': 'AU',\n",
    "    'types': ['country', 'political']},\n",
    "   {'long_name': '3170', 'short_name': '3170', 'types': ['postal_code']}],\n",
    "  'formatted_address': '623-631 Ferntree Gully Rd (Corner, Springvale Rd, Glen Waverley VIC 3170, Australia',\n",
    "  'geometry': {'location': {'lat': -37.9031611, 'lng': 145.1621324},\n",
    "   'location_type': 'GEOMETRIC_CENTER',\n",
    "   'viewport': {'northeast': {'lat': -37.9018121197085,\n",
    "     'lng': 145.1634813802915},\n",
    "    'southwest': {'lat': -37.9045100802915, 'lng': 145.1607834197085}}},\n",
    "  'place_id': 'ChIJc-Gwlh4V1moRwzPrZVjaUaI',\n",
    "  'plus_code': {'compound_code': '35W6+PV Glen Waverley VIC, Australia',\n",
    "   'global_code': '4RJ735W6+PV'},\n",
    "  'types': ['convenience_store',\n",
    "   'establishment',\n",
    "   'food',\n",
    "   'gas_station',\n",
    "   'point_of_interest',\n",
    "   'store']}), \n",
    " 61301436:json.dumps({'address_components': [{'long_name': '1',\n",
    "    'short_name': '1',\n",
    "    'types': ['street_number']},\n",
    "   {'long_name': 'Kings Road', 'short_name': 'Kings Rd', 'types': ['route']},\n",
    "   {'long_name': 'Kings Park',\n",
    "    'short_name': 'Kings Park',\n",
    "    'types': ['locality', 'political']},\n",
    "   {'long_name': 'City of Brimbank',\n",
    "    'short_name': 'City of Brimbank',\n",
    "    'types': ['administrative_area_level_2', 'political']},\n",
    "   {'long_name': 'Victoria',\n",
    "    'short_name': 'VIC',\n",
    "    'types': ['administrative_area_level_1', 'political']},\n",
    "   {'long_name': 'Australia',\n",
    "    'short_name': 'AU',\n",
    "    'types': ['country', 'political']},\n",
    "   {'long_name': '3021', 'short_name': '3021', 'types': ['postal_code']}],\n",
    "  'formatted_address': '1 Kings Rd, Kings Park VIC 3021, Australia',\n",
    "  'geometry': {'location': {'lat': -37.741633, 'lng': 144.778864},\n",
    "   'location_type': 'ROOFTOP',\n",
    "   'viewport': {'northeast': {'lat': -37.7402840197085,\n",
    "     'lng': 144.7802129802915},\n",
    "    'southwest': {'lat': -37.7429819802915, 'lng': 144.7775150197085}}},\n",
    "  'place_id': 'ChIJc7OQn2T11moRf98tgIJGrMc',\n",
    "  'plus_code': {'compound_code': '7Q5H+8G Kings Park VIC, Australia',\n",
    "   'global_code': '4RJ67Q5H+8G'},\n",
    "  'types': ['atm',\n",
    "   'bakery',\n",
    "   'convenience_store',\n",
    "   'establishment',\n",
    "   'finance',\n",
    "   'food',\n",
    "   'gas_station',\n",
    "   'point_of_interest',\n",
    "   'restaurant',\n",
    "   'store']}), \n",
    " 61301034:json.dumps({'address_components': [{'long_name': '556',\n",
    "    'short_name': '556',\n",
    "    'types': ['street_number']},\n",
    "   {'long_name': 'Mount Alexander Road',\n",
    "    'short_name': 'Mt Alexander Rd',\n",
    "    'types': ['route']},\n",
    "   {'long_name': 'Moonee Ponds',\n",
    "    'short_name': 'Moonee Ponds',\n",
    "    'types': ['locality', 'political']},\n",
    "   {'long_name': 'City of Moonee Valley',\n",
    "    'short_name': 'City of Moonee Valley',\n",
    "    'types': ['administrative_area_level_2', 'political']},\n",
    "   {'long_name': 'Victoria',\n",
    "    'short_name': 'VIC',\n",
    "    'types': ['administrative_area_level_1', 'political']},\n",
    "   {'long_name': 'Australia',\n",
    "    'short_name': 'AU',\n",
    "    'types': ['country', 'political']},\n",
    "   {'long_name': '3039', 'short_name': '3039', 'types': ['postal_code']}],\n",
    "  'formatted_address': '556 Mt Alexander Rd, Moonee Ponds VIC 3039, Australia',\n",
    "  'geometry': {'location': {'lat': -37.7732195, 'lng': 144.9273746},\n",
    "   'location_type': 'ROOFTOP',\n",
    "   'viewport': {'northeast': {'lat': -37.7718705197085,\n",
    "     'lng': 144.9287235802915},\n",
    "    'southwest': {'lat': -37.7745684802915, 'lng': 144.9260256197085}}},\n",
    "  'place_id': 'ChIJxftvvPpc1moRGuD2aqk6MQo',\n",
    "  'plus_code': {'compound_code': '6WGG+PW Moonee Ponds VIC, Australia',\n",
    "   'global_code': '4RJ66WGG+PW'},\n",
    "  'types': ['atm',\n",
    "   'bakery',\n",
    "   'establishment',\n",
    "   'finance',\n",
    "   'food',\n",
    "   'gas_station',\n",
    "   'point_of_interest',\n",
    "   'restaurant',\n",
    "   'store']}),\n",
    " 61303190:json.dumps({'address_components': [{'long_name': '108',\n",
    "    'short_name': '108',\n",
    "    'types': ['street_number']},\n",
    "   {'long_name': 'Highett Road',\n",
    "    'short_name': 'Highett Rd',\n",
    "    'types': ['route']},\n",
    "   {'long_name': 'Sandringham',\n",
    "    'short_name': 'Sandringham',\n",
    "    'types': ['locality', 'political']},\n",
    "   {'long_name': 'City of Bayside',\n",
    "    'short_name': 'City of Bayside',\n",
    "    'types': ['administrative_area_level_2', 'political']},\n",
    "   {'long_name': 'Victoria',\n",
    "    'short_name': 'VIC',\n",
    "    'types': ['administrative_area_level_1', 'political']},\n",
    "   {'long_name': 'Australia',\n",
    "    'short_name': 'AU',\n",
    "    'types': ['country', 'political']},\n",
    "   {'long_name': '3188', 'short_name': '3188', 'types': ['postal_code']}],\n",
    "  'formatted_address': '108 Highett Rd, Sandringham VIC 3188, Australia',\n",
    "  'geometry': {'location': {'lat': -37.9466107, 'lng': 145.0224392},\n",
    "   'location_type': 'ROOFTOP',\n",
    "   'viewport': {'northeast': {'lat': -37.9452617197085,\n",
    "     'lng': 145.0237881802915},\n",
    "    'southwest': {'lat': -37.9479596802915, 'lng': 145.0210902197085}}},\n",
    "  'place_id': 'ChIJjbeUMepu1moR-6cVYWYHU_w',\n",
    "  'plus_code': {'compound_code': '323C+9X Sandringham VIC, Australia',\n",
    "   'global_code': '4RJ7323C+9X'},\n",
    "  'types': ['atm',\n",
    "   'bakery',\n",
    "   'convenience_store',\n",
    "   'establishment',\n",
    "   'finance',\n",
    "   'food',\n",
    "   'gas_station',\n",
    "   'point_of_interest',\n",
    "   'restaurant',\n",
    "   'store']})})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a32d5f8f-5c08-45c3-b63a-c535b983caa6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_out = df_gadd[['sitecode', 'sitename', 'address1', 'bn_prd']].copy()\n",
    "df_out['gmap'] = df_out['sitecode'].map(d_matches)\n",
    "display(df_out[df_out['gmap'].isnull()])\n",
    "df_out = df_out[df_out['gmap'].notnull()].copy()\n",
    "df_out[['gadd', 'coor', 'gpost']] = df_out['gmap'].apply(lambda x: myf_get_address(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2de975a9-2417-40c6-8273-1a3acb4a79b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "display(df_out[df_out.groupby('gadd')['sitecode'].transform('count')>1].sort_values('gadd'))\n",
    "l_check = df_out.loc[df_out.groupby('gadd')['sitecode'].transform('count')>1, 'gadd'].unique().tolist()\n",
    "l_check.sort()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "626b619b-7d5b-4310-9628-05ff93d491ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_out.to_csv(\"./Output/is_vic_gmap_clean.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4994fb00-051e-418f-856d-ad2ae495d37b",
   "metadata": {},
   "source": [
    "# Extending the sample to from 2005 to 2019\n",
    "\n",
    "## obtain gmap addresses and geocoordinates using Google Map API for new stations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "be7d3a6c-545d-45ea-95f8-068fca90a914",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_old_schema = pd.read_csv(\"./Output/is_vic_gmap_clean.csv\")\n",
    "print(df_old_schema[['sitecode', 'coor']].nunique())\n",
    "display(df_old_schema.head(2))\n",
    "l_old = df_old_schema['sitecode'].unique().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "43534b54-65dc-4e8c-9570-d630a537e541",
   "metadata": {},
   "outputs": [],
   "source": [
    "l_is_st = df0['sitecode'].unique().tolist()\n",
    "print(len(l_is_st))\n",
    "l_is_new = [i for i in l_is_st if i not in l_old]\n",
    "print('Number of new stations: {}'.format(len(l_is_new)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "401040ee-44d5-4080-aa71-bb194812f13c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_add_st = df_vic.loc[df_vic['sitecode'].isin(l_is_new)].groupby('sitecode')[\n",
    "    ['brand', 'sitename', 'address1', 'suburb']].last().reset_index().copy()\n",
    "print(df_add_st.shape)\n",
    "df_add_st.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fc1bc463-be92-4523-869d-5e1c38297c41",
   "metadata": {},
   "outputs": [],
   "source": [
    "gmaps = googlemaps.Client(key='Your_API_Key')\n",
    "df_add_st['gmap_result'] = None\n",
    "\n",
    "for ix, ir in df_add_st.iterrows():\n",
    "    try: \n",
    "        geocode_result = gmaps.geocode(ir['sitename']+', '+ir['address1']+', '+ir['suburb']+', '+'VIC ')\n",
    "    except:\n",
    "        geocode_result = []\n",
    "        print('error: ' + ir['sitecode'])\n",
    "    if len(geocode_result)>0:\n",
    "        df_add_st.loc[ix, 'gmap_result'] = json.dumps(geocode_result[0])\n",
    "    else:\n",
    "        df_add_st.loc[ix, 'gmap_result'] = \"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c3470c2a-f07b-4e6b-9163-a5af72d29b82",
   "metadata": {},
   "outputs": [],
   "source": [
    "def myf_get_address(x):  \n",
    "    x = json.loads(x)\n",
    "    try:\n",
    "        out_add = x['formatted_address'].upper() \n",
    "    except: out_add = ''\n",
    "    try: \n",
    "        out_lat = round(x['geometry']['location']['lat'], 8)\n",
    "    except: out_lat = np.nan\n",
    "    try: \n",
    "        out_lng = round(x['geometry']['location']['lng'], 8)\n",
    "    except: out_lng = np.nan\n",
    "    try: \n",
    "        out_list = [i for i in x['address_components'] if 'post' in i['types'][0]]\n",
    "        out_post = int(out_list[0]['long_name'])\n",
    "    except: out_post = np.nan\n",
    "    return pd.Series((out_add, '({}, {})'.format(out_lat, out_lng), out_post))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "df32d681-1791-4ae4-8307-b99a5841edb6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_add_st[['gadd', 'coor', 'gpostcode']] = df_add_st['gmap_result'].apply(\n",
    "    lambda x: myf_get_address(x))\n",
    "df_add_st['brand'] = df_add_st['sitecode'].map(df_vic.groupby('sitecode')['brand'].last())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "130d2889-b3ba-410a-b302-3dc14e795a05",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_add_st.loc[df_add_st['sitecode']==61377117, 'gmap_result'] = json.dumps({'address_components': [{'long_name': '110',\n",
    "   'short_name': '110',\n",
    "   'types': ['street_number']},\n",
    "  {'long_name': 'Harvest Home Road',\n",
    "   'short_name': 'Harvest Home Rd',\n",
    "   'types': ['route']},\n",
    "  {'long_name': 'Wollert',\n",
    "   'short_name': 'Wollert',\n",
    "   'types': ['locality', 'political']},\n",
    "  {'long_name': 'City of Whittlesea',\n",
    "   'short_name': 'City of Whittlesea',\n",
    "   'types': ['administrative_area_level_2', 'political']},\n",
    "  {'long_name': 'Victoria',\n",
    "   'short_name': 'VIC',\n",
    "   'types': ['administrative_area_level_1', 'political']},\n",
    "  {'long_name': 'Australia',\n",
    "   'short_name': 'AU',\n",
    "   'types': ['country', 'political']},\n",
    "  {'long_name': '3750', 'short_name': '3750', 'types': ['postal_code']}],\n",
    " 'formatted_address': '110 Harvest Home Rd, Wollert VIC 3750, Australia',\n",
    " 'geometry': {'bounds': {'northeast': {'lat': -37.6226692, 'lng': 145.029707},\n",
    "   'southwest': {'lat': -37.6229187, 'lng': 145.0295069}},\n",
    "  'location': {'lat': -37.6228021, 'lng': 145.0296576},\n",
    "  'location_type': 'ROOFTOP',\n",
    "  'viewport': {'northeast': {'lat': -37.62144496970849,\n",
    "    'lng': 145.0309620802915},\n",
    "   'southwest': {'lat': -37.62414293029149, 'lng': 145.0282641197085}}},\n",
    " 'partial_match': True,\n",
    " 'place_id': 'ChIJO8gZ_BhM1moRyacp_0qiaSI',\n",
    " 'types': ['premise']})\n",
    "\n",
    "df_add_st.loc[df_add_st['sitecode']==61370046, 'gmap_result'] = json.dumps({'address_components': [{'long_name': '21',\n",
    "   'short_name': '21',\n",
    "   'types': ['street_number']},\n",
    "  {'long_name': 'Princes Street',\n",
    "   'short_name': 'Princes St',\n",
    "   'types': ['route']},\n",
    "  {'long_name': 'Traralgon',\n",
    "   'short_name': 'Traralgon',\n",
    "   'types': ['locality', 'political']},\n",
    "  {'long_name': 'Latrobe City',\n",
    "   'short_name': 'Latrobe',\n",
    "   'types': ['administrative_area_level_2', 'political']},\n",
    "  {'long_name': 'Victoria',\n",
    "   'short_name': 'VIC',\n",
    "   'types': ['administrative_area_level_1', 'political']},\n",
    "  {'long_name': 'Australia',\n",
    "   'short_name': 'AU',\n",
    "   'types': ['country', 'political']},\n",
    "  {'long_name': '3844', 'short_name': '3844', 'types': ['postal_code']}],\n",
    " 'formatted_address': '21 Princes St, Traralgon VIC 3844, Australia',\n",
    " 'geometry': {'location': {'lat': -38.198025, 'lng': 146.540172},\n",
    "  'location_type': 'ROOFTOP',\n",
    "  'viewport': {'northeast': {'lat': -38.1965932197085,\n",
    "    'lng': 146.5414837802915},\n",
    "   'southwest': {'lat': -38.1992911802915, 'lng': 146.5387858197085}}},\n",
    " 'partial_match': True,\n",
    " 'place_id': 'ChIJM7-UTh8YKWsR7bRd-HCy_HA',\n",
    " 'plus_code': {'compound_code': 'RG2R+Q3 Traralgon VIC, Australia',\n",
    "  'global_code': '4RH8RG2R+Q3'},\n",
    " 'types': ['convenience_store',\n",
    "  'establishment',\n",
    "  'food',\n",
    "  'gas_station',\n",
    "  'point_of_interest',\n",
    "  'store']})\n",
    "\n",
    "df_add_st.loc[df_add_st['sitecode']==61301459, 'gmap_result'] = json.dumps({'address_components': [{'long_name': '374',\n",
    "   'short_name': '374',\n",
    "   'types': ['street_number']},\n",
    "  {'long_name': 'Princes Highway',\n",
    "   'short_name': 'Princes Hwy',\n",
    "   'types': ['route']},\n",
    "  {'long_name': 'Traralgon East',\n",
    "   'short_name': 'Traralgon East',\n",
    "   'types': ['locality', 'political']},\n",
    "  {'long_name': 'Latrobe City',\n",
    "   'short_name': 'Latrobe',\n",
    "   'types': ['administrative_area_level_2', 'political']},\n",
    "  {'long_name': 'Victoria',\n",
    "   'short_name': 'VIC',\n",
    "   'types': ['administrative_area_level_1', 'political']},\n",
    "  {'long_name': 'Australia',\n",
    "   'short_name': 'AU',\n",
    "   'types': ['country', 'political']},\n",
    "  {'long_name': '3844', 'short_name': '3844', 'types': ['postal_code']}],\n",
    " 'formatted_address': '374 Princes Hwy, Traralgon East VIC 3844, Australia',\n",
    " 'geometry': {'location': {'lat': -38.1896359, 'lng': 146.5720565},\n",
    "  'location_type': 'ROOFTOP',\n",
    "  'viewport': {'northeast': {'lat': -38.18817961970849,\n",
    "    'lng': 146.5735781802915},\n",
    "   'southwest': {'lat': -38.19087758029149, 'lng': 146.5708802197085}}},\n",
    " 'place_id': 'ChIJT7bJmpkiKWsRA1moYxsHsUQ',\n",
    " 'plus_code': {'compound_code': 'RH6C+4R Traralgon East VIC, Australia',\n",
    "  'global_code': '4RH8RH6C+4R'},\n",
    " 'types': ['establishment', 'gas_station', 'point_of_interest']})\n",
    "\n",
    "df_add_st.loc[df_add_st['sitecode']==61303573, 'gmap_result'] = json.dumps({'address_components': [{'long_name': '55',\n",
    "   'short_name': '55',\n",
    "   'types': ['street_number']},\n",
    "  {'long_name': 'Whyte Street', 'short_name': 'Whyte St', 'types': ['route']},\n",
    "  {'long_name': 'Coleraine',\n",
    "   'short_name': 'Coleraine',\n",
    "   'types': ['locality', 'political']},\n",
    "  {'long_name': 'Southern Grampians Shire',\n",
    "   'short_name': 'Southern Grampians',\n",
    "   'types': ['administrative_area_level_2', 'political']},\n",
    "  {'long_name': 'Victoria',\n",
    "   'short_name': 'VIC',\n",
    "   'types': ['administrative_area_level_1', 'political']},\n",
    "  {'long_name': 'Australia',\n",
    "   'short_name': 'AU',\n",
    "   'types': ['country', 'political']},\n",
    "  {'long_name': '3315', 'short_name': '3315', 'types': ['postal_code']}],\n",
    " 'formatted_address': '55 Whyte St, Coleraine VIC 3315, Australia',\n",
    " 'geometry': {'bounds': {'northeast': {'lat': -37.5986956, 'lng': 141.6942868},\n",
    "   'southwest': {'lat': -37.5989388, 'lng': 141.6941515}},\n",
    "  'location': {'lat': -37.5988191, 'lng': 141.6941944},\n",
    "  'location_type': 'ROOFTOP',\n",
    "  'viewport': {'northeast': {'lat': -37.59739936970849,\n",
    "    'lng': 141.6955681302915},\n",
    "   'southwest': {'lat': -37.60009733029149, 'lng': 141.6928701697085}}},\n",
    " 'place_id': 'ChIJk229_rn0zGoRJMJnLwFLByo',\n",
    " 'types': ['premise']})\n",
    "# long exited\n",
    "df_add_st.loc[df_add_st['sitecode']==61377012, 'gmap_result'] = json.dumps({'address_components': [{'long_name': '228-234',\n",
    "   'short_name': '228-234',\n",
    "   'types': ['street_number']},\n",
    "  {'long_name': 'High Street', 'short_name': 'High St', 'types': ['route']},\n",
    "  {'long_name': 'Shepparton',\n",
    "   'short_name': 'Shepparton',\n",
    "   'types': ['locality', 'political']},\n",
    "  {'long_name': 'Greater Shepparton City',\n",
    "   'short_name': 'Greater Shepparton',\n",
    "   'types': ['administrative_area_level_2', 'political']},\n",
    "  {'long_name': 'Victoria',\n",
    "   'short_name': 'VIC',\n",
    "   'types': ['administrative_area_level_1', 'political']},\n",
    "  {'long_name': 'Australia',\n",
    "   'short_name': 'AU',\n",
    "   'types': ['country', 'political']},\n",
    "  {'long_name': '3630', 'short_name': '3630', 'types': ['postal_code']}],\n",
    " 'formatted_address': '228-234 High St, Shepparton VIC 3630, Australia',\n",
    " 'geometry': {'location': {'lat': -36.3822008, 'lng': 145.4059876},\n",
    "  'location_type': 'ROOFTOP',\n",
    "  'viewport': {'northeast': {'lat': -36.3807873197085,\n",
    "    'lng': 145.4073541802915},\n",
    "   'southwest': {'lat': -36.3834852802915, 'lng': 145.4046562197085}}},\n",
    " 'place_id': 'ChIJ8Z2jcfeD2GoRgQ2NVQ0CB9w',\n",
    " 'plus_code': {'compound_code': 'JC94+49 Shepparton VIC, Australia',\n",
    "  'global_code': '4RM7JC94+49'},\n",
    " 'types': ['street_address']})\n",
    "\n",
    "df_add_st.loc[df_add_st['sitecode']==61303563, 'gmap_result'] = json.dumps({'address_components': [{'long_name': 'Scott &',\n",
    "   'short_name': 'Scott &',\n",
    "   'types': ['establishment', 'point_of_interest']},\n",
    "  {'long_name': 'Lyle Street', 'short_name': 'Lyle St', 'types': ['route']},\n",
    "  {'long_name': 'Warracknabeal',\n",
    "   'short_name': 'Warracknabeal',\n",
    "   'types': ['locality', 'political']},\n",
    "  {'long_name': 'Yarriambiack Shire',\n",
    "   'short_name': 'Yarriambiack',\n",
    "   'types': ['administrative_area_level_2', 'political']},\n",
    "  {'long_name': 'Victoria',\n",
    "   'short_name': 'VIC',\n",
    "   'types': ['administrative_area_level_1', 'political']},\n",
    "  {'long_name': 'Australia',\n",
    "   'short_name': 'AU',\n",
    "   'types': ['country', 'political']},\n",
    "  {'long_name': '3393', 'short_name': '3393', 'types': ['postal_code']}],\n",
    " 'formatted_address': '170 Scott Street (Cnr, Scott &, Lyle St, Warracknabeal VIC 3393, Australia',\n",
    " 'geometry': {'location': {'lat': -36.2528705, 'lng': 142.3947177},\n",
    "  'location_type': 'GEOMETRIC_CENTER',\n",
    "  'viewport': {'northeast': {'lat': -36.2516689697085,\n",
    "    'lng': 142.3961120802915},\n",
    "   'southwest': {'lat': -36.2543669302915, 'lng': 142.3934141197085}}},\n",
    " 'partial_match': True,\n",
    " 'place_id': 'ChIJHW0WH33rz2oR-Rf8dTrtypc',\n",
    " 'plus_code': {'compound_code': 'P9WV+VV Warracknabeal VIC, Australia',\n",
    "  'global_code': '4RM4P9WV+VV'},\n",
    " 'types': ['establishment', 'gas_station', 'point_of_interest']})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a1424154-cf1a-49a6-bab0-23c6ec1a9edd",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_add_st[['gadd', 'coor', 'gpostcode']] = df_add_st['gmap_result'].apply(\n",
    "    lambda x: myf_get_address(x))\n",
    "df_add_st['brand'] = df_add_st['sitecode'].map(df_vic.groupby('sitecode')['brand'].last())\n",
    "df_add_st[['sitecode', 'address1', 'coor']].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6fca4e9e-5621-4b9c-87a2-966e1b75ee17",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sitecode_coor = pd.concat(\n",
    "    [df_old_schema[['sitecode', 'sitename', 'address1', 'coor', 'gadd', 'gmap']], \n",
    "     df_add_st[['sitecode', 'sitename', 'brand', 'address1', 'suburb', 'coor', 'gadd', 'gmap_result']].rename(\n",
    "         columns={'gmap_result':'gmap'})], axis=0).copy()\n",
    "print(df_sitecode_coor[['sitecode', 'coor']].nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d3bda73f-e6e2-4efb-8b04-eefa5d8b9209",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sitecode_coor['sitecode_new'] = df_sitecode_coor['sitecode']\n",
    "df_sitecode_coor.loc[df_sitecode_coor['sitecode']==61303726, 'sitecode_new'] = 61301485\n",
    "df_sitecode_coor.loc[df_sitecode_coor['sitecode']==61301899, 'sitecode_new'] = 61377017\n",
    "df_sitecode_coor.loc[df_sitecode_coor['sitecode']==61303631, 'sitecode_new'] = 61351481\n",
    "\n",
    "df_sitecode_coor.loc[df_sitecode_coor['sitecode']==61302434, 'gmap'] = json.dumps({'address_components': [{'long_name': '271',\n",
    "    'short_name': '271',\n",
    "    'types': ['street_number']},\n",
    "   {'long_name': 'Barker Street',\n",
    "    'short_name': 'Barker St',\n",
    "    'types': ['route']},\n",
    "   {'long_name': 'Castlemaine',\n",
    "    'short_name': 'Castlemaine',\n",
    "    'types': ['locality', 'political']},\n",
    "   {'long_name': 'Mount Alexander Shire',\n",
    "    'short_name': 'Mount Alexander',\n",
    "    'types': ['administrative_area_level_2', 'political']},\n",
    "   {'long_name': 'Victoria',\n",
    "    'short_name': 'VIC',\n",
    "    'types': ['administrative_area_level_1', 'political']},\n",
    "   {'long_name': 'Australia',\n",
    "    'short_name': 'AU',\n",
    "    'types': ['country', 'political']},\n",
    "   {'long_name': '3450', 'short_name': '3450', 'types': ['postal_code']}],\n",
    "  'formatted_address': '271 Barker St, Castlemaine VIC 3450, Australia',\n",
    "  'geometry': {'bounds': {'northeast': {'lat': -37.0617311,\n",
    "     'lng': 144.2169631},\n",
    "    'southwest': {'lat': -37.0618935, 'lng': 144.2168149}},\n",
    "   'location': {'lat': -37.061775, 'lng': 144.216859},\n",
    "   'location_type': 'ROOFTOP',\n",
    "   'viewport': {'northeast': {'lat': -37.0604633197085,\n",
    "     'lng': 144.2184365802915},\n",
    "    'southwest': {'lat': -37.06316128029149, 'lng': 144.2157386197085}}},\n",
    "  'place_id': 'ChIJGXadadgw12oRkMOXVO_cNLg',\n",
    "  'types': ['premise']})\n",
    "\n",
    "df_sitecode_coor.loc[df_sitecode_coor['sitecode']==61301406, 'gmap'] = json.dumps({'address_components': [{'long_name': '1745',\n",
    "    'short_name': '1745',\n",
    "    'types': ['street_number']},\n",
    "   {'long_name': 'Sydney Road', 'short_name': 'Sydney Rd', 'types': ['route']},\n",
    "   {'long_name': 'Campbellfield',\n",
    "    'short_name': 'Campbellfield',\n",
    "    'types': ['locality', 'political']},\n",
    "   {'long_name': 'City of Hume',\n",
    "    'short_name': 'Hume',\n",
    "    'types': ['administrative_area_level_2', 'political']},\n",
    "   {'long_name': 'Victoria',\n",
    "    'short_name': 'VIC',\n",
    "    'types': ['administrative_area_level_1', 'political']},\n",
    "   {'long_name': 'Australia',\n",
    "    'short_name': 'AU',\n",
    "    'types': ['country', 'political']},\n",
    "   {'long_name': '3061', 'short_name': '3061', 'types': ['postal_code']}],\n",
    "  'formatted_address': '1745 Sydney Rd, Campbellfield VIC 3061, Australia',\n",
    "  'geometry': {'location': {'lat': -37.6510775, 'lng': 144.9529753},\n",
    "   'location_type': 'ROOFTOP',\n",
    "   'viewport': {'northeast': {'lat': -37.6497425697085,\n",
    "     'lng': 144.9541688302915},\n",
    "    'southwest': {'lat': -37.6524405302915, 'lng': 144.9514708697085}}},\n",
    "  'place_id': 'ChIJN61MbyVQ1moR00W3G7izjnQ',\n",
    "  'plus_code': {'compound_code': '8XX3+H5 Campbellfield VIC, Australia',\n",
    "   'global_code': '4RJ68XX3+H5'},\n",
    "  'types': ['street_address']})\n",
    "\n",
    "df_sitecode_coor.loc[df_sitecode_coor['sitecode']==61302061, 'gmap'] = json.dumps({'address_components': [{'long_name': '229',\n",
    "    'short_name': '229',\n",
    "    'types': ['street_number']},\n",
    "   {'long_name': 'Blackburn Road',\n",
    "    'short_name': 'Blackburn Rd',\n",
    "    'types': ['route']},\n",
    "   {'long_name': 'Blackburn South',\n",
    "    'short_name': 'Blackburn South',\n",
    "    'types': ['locality', 'political']},\n",
    "   {'long_name': 'Whitehorse City',\n",
    "    'short_name': 'Whitehorse',\n",
    "    'types': ['administrative_area_level_2', 'political']},\n",
    "   {'long_name': 'Victoria',\n",
    "    'short_name': 'VIC',\n",
    "    'types': ['administrative_area_level_1', 'political']},\n",
    "   {'long_name': 'Australia',\n",
    "    'short_name': 'AU',\n",
    "    'types': ['country', 'political']},\n",
    "   {'long_name': '3130', 'short_name': '3130', 'types': ['postal_code']}],\n",
    "  'formatted_address': '229 Blackburn Rd, Blackburn South VIC 3130, Australia',\n",
    "  'geometry': {'location': {'lat': -37.8410221, 'lng': 145.1556273},\n",
    "   'location_type': 'ROOFTOP',\n",
    "   'viewport': {'northeast': {'lat': -37.8396600197085,\n",
    "     'lng': 145.1568636302915},\n",
    "    'southwest': {'lat': -37.8423579802915, 'lng': 145.1541656697085}}},\n",
    "  'place_id': 'ChIJv3gTbWo_1moRVpZAzGfn2tw',\n",
    "  'plus_code': {'compound_code': '5554+H7 Blackburn South VIC, Australia',\n",
    "   'global_code': '4RJ75554+H7'},\n",
    "  'types': ['street_address']})\n",
    "\n",
    "df_sitecode_coor.loc[df_sitecode_coor['sitecode']==61301242, 'gmap'] = json.dumps({'address_components': [{'long_name': '43-45',\n",
    "   'short_name': '43-45',\n",
    "   'types': ['street_number']},\n",
    "  {'long_name': 'Vesper Drive', 'short_name': 'Vesper Dr', 'types': ['route']},\n",
    "  {'long_name': 'Narre Warren',\n",
    "   'short_name': 'Narre Warren',\n",
    "   'types': ['locality', 'political']},\n",
    "  {'long_name': 'City of Casey',\n",
    "   'short_name': 'Casey',\n",
    "   'types': ['administrative_area_level_2', 'political']},\n",
    "  {'long_name': 'Victoria',\n",
    "   'short_name': 'VIC',\n",
    "   'types': ['administrative_area_level_1', 'political']},\n",
    "  {'long_name': 'Australia',\n",
    "   'short_name': 'AU',\n",
    "   'types': ['country', 'political']},\n",
    "  {'long_name': '3805', 'short_name': '3805', 'types': ['postal_code']}],\n",
    " 'formatted_address': '43-45 Vesper Dr, Narre Warren VIC 3805, Australia',\n",
    " 'geometry': {'location': {'lat': -38.0187925, 'lng': 145.2948489},\n",
    "  'location_type': 'ROOFTOP',\n",
    "  'viewport': {'northeast': {'lat': -38.01749221970849,\n",
    "    'lng': 145.2962077802915},\n",
    "   'southwest': {'lat': -38.02019018029149, 'lng': 145.2935098197085}}},\n",
    " 'place_id': 'ChIJ35VsJrUQ1moRvcg_bzWOl3s',\n",
    " 'plus_code': {'compound_code': 'X7JV+FW Narre Warren VIC, Australia',\n",
    "  'global_code': '4RH7X7JV+FW'},\n",
    " 'types': ['street_address']})\n",
    "\n",
    "df_sitecode_coor.loc[df_sitecode_coor['sitecode']==61301682, 'gmap'] = json.dumps({'address_components': [{'long_name': '117',\n",
    "   'short_name': '117',\n",
    "   'types': ['street_number']},\n",
    "  {'long_name': 'Archer Street',\n",
    "   'short_name': 'Archer St',\n",
    "   'types': ['route']},\n",
    "  {'long_name': 'Shepparton',\n",
    "   'short_name': 'Shepparton',\n",
    "   'types': ['locality', 'political']},\n",
    "  {'long_name': 'Greater Shepparton City',\n",
    "   'short_name': 'Greater Shepparton',\n",
    "   'types': ['administrative_area_level_2', 'political']},\n",
    "  {'long_name': 'Victoria',\n",
    "   'short_name': 'VIC',\n",
    "   'types': ['administrative_area_level_1', 'political']},\n",
    "  {'long_name': 'Australia',\n",
    "   'short_name': 'AU',\n",
    "   'types': ['country', 'political']},\n",
    "  {'long_name': '3630', 'short_name': '3630', 'types': ['postal_code']}],\n",
    " 'formatted_address': '117 Archer St, Shepparton VIC 3630, Australia',\n",
    " 'geometry': {'bounds': {'northeast': {'lat': -36.3872975, 'lng': 145.4123482},\n",
    "   'southwest': {'lat': -36.3874157, 'lng': 145.4121221}},\n",
    "  'location': {'lat': -36.3873536, 'lng': 145.4122308},\n",
    "  'location_type': 'ROOFTOP',\n",
    "  'viewport': {'northeast': {'lat': -36.3860076197085,\n",
    "    'lng': 145.4134772802915},\n",
    "   'southwest': {'lat': -36.3887055802915, 'lng': 145.4107793197085}}},\n",
    " 'place_id': 'ChIJaVzx4fqD2GoRxSuOEiZzcKM',\n",
    " 'types': ['premise']})\n",
    "\n",
    "df_sitecode_coor[['gadd', 'coor', 'gpostcode']] = df_sitecode_coor['gmap'].apply(\n",
    "    lambda x: myf_get_address(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a03f915d-58b7-4bfe-829d-e23e3f59d362",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sitecode_coor[['sitecode', 'sitecode_new', 'coor']].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e42c6d3e-a9cb-4e37-b97d-0a80f1aaa5bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sitecode_coor.to_csv(\"./Output/is_vic_gmap_clean_2005_2019.csv\", index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
